package edu.tamu.cse.teamproject1.AirlineApplication;

import java.util.*;
import java.io.*;

import edu.tamu.cse.teamproject1.Database.ColumnDefinition;
import edu.tamu.cse.teamproject1.Database.EnumColumnType;
import edu.tamu.cse.teamproject1.Database.Table;
import edu.tamu.cse.teamproject1.Database.Tuple;
import edu.tamu.cse.teamproject1.Database.TupleElement;
import edu.tamu.cse.teamproject1.Database.Exceptions.SqlException;
import edu.tamu.cse.teamproject1.Database.Interfaces.DatabaseAPI;
import edu.tamu.cse.teamproject1.Database.Interfaces.IColumnDefinition;
import edu.tamu.cse.teamproject1.Database.Interfaces.IDatabaseTable;
import edu.tamu.cse.teamproject1.Database.Interfaces.ITuple;
import edu.tamu.cse.teamproject1.Database.Interfaces.ITupleElement;
import edu.tamu.cse.teamproject1.Database.Interfaces.IXMLParser;
import edu.tamu.cse.teamproject1.Database.Persistence.XMLParser;

/**
 * @author Ryan McAuley
 * This class is basically an application ng the correctness and
 * consistency of our database. This is something that a typical client
 * would use when implementing our API.
 */

public class testApplication {
	static DatabaseAPI airplaneApplication;
	
	public static void main(String args[]) throws IOException{
		/*Display Welcome Message*/
		welcomeGraphic();
		
		/*Query Level*/
		int menuOption = -1;
		String userInput = new String();
		displayMenu();
		
		while(menuOption != 0){
			airplaneApplication = getXMLDatabase();
			
			//the table that will display the view created by the SQL statements 
			IDatabaseTable tableView = null;
			
			System.out.println("Please Enter Your Menu Selection: ");
			BufferedReader br = new BufferedReader(new InputStreamReader(System.in) );
			try {
			userInput = br.readLine();
			}	catch (IOException e) {
					e.printStackTrace();
				}
			menuOption = Integer.parseInt(userInput);
			
			switch(menuOption){
				//Exit Command
				case 0:
					System.out.println("Thank you! Have a nice day!");
					return;
				//List All Flights
				case 1:
					tableView = listAllFlights();
					break;
				//List All Passengers
				case 2:
					tableView = listAllPassengers();
					break;
				//List Ticket Information
				case 3:
					tableView = listAllTicketInfo();
					break;
				//Rename to Espanol
				case 4:
					tableView = convertToEspanol();
					break;
				//Count Unique Flights
				case 5:
					tableView = listUniqueFlightCount();
					break;
				//Find ticket with lowest cost
				case 6:
			        tableView = listLowestCostFlight();
					break;
				//List Information about Old planes
				case 7:
					tableView = findOldPlanesDueToFlyOut();
					break;
				//List all tickets that are higher than $100
				case 8:
					tableView = findTicketsGTHundred();
					break;
				//Find High Risk Planes
				case 9:
					tableView = findHighRiskPlanes();
					break;
				//Return Last Person in the List using the All command
				case 10:
					tableView = findLastPersonInList();
					break;
				//Return Passengers from ticket info
				case 11:
					
					tableView = getPassengersFromTicketInfo();
					break;
				case 12:
					System.out.println("Please enter the PassengerID: ");
					String passengerID = "";
					passengerID = br.readLine();
					tableView = getTicketsByPassengerID(passengerID);
				//Erroneous Menu Input
				default:
					System.out.println("Invalid Menu Command!");
					break;	
			}
			if (tableView != null) {
				viewTable(tableView);
			}
		}
	}
	
	static DatabaseAPI getXMLDatabase() throws IOException{
		IXMLParser parseXML = new XMLParser();
		parseXML.createTableStructure("applicationTables.xml");
		parseXML.fillTable("applicationData.xml");
		return parseXML.getGeneratedDatabase();
	}
	
	/**
	 *
	 *Prints the menu to the console
	 *@param void
	 *@return void
	 *
	 */
	static void displayMenu(){
		System.out.println("Menu Options:");
		System.out.println("0: Exit Application");
		System.out.println("1: List All Flight Information");
		System.out.println("2: List All Passenger Information"); 
		System.out.println("3: List All Ticket Information"); 
		System.out.println("4: Rename Flight Number to Espanol");
		System.out.println("5: List Number of All Unique Flights");
		System.out.println("6: List Flight with the Lowest Cost");
		System.out.println("7: List Old Planes due to Fly Out");
		System.out.println("8: Find All Tickets which Cost More Than $100");
		System.out.println("9: Find High Risk Planes");
		System.out.println("10: Return the Last Person Registered");
		System.out.println("11: Return Passengers from Ticket Info");
		System.out.println("12: Get Tickets by Passenger ID");
	}

	/**
	 *
	 * 1) Lists all flights in a view
	 *@param void
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable listAllFlights(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT * FROM FlightInfo");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * 2) Lists all passengers in a view
	 *@returns IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable listAllPassengers(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT * FROM PassengerInfo");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * 3) Lists all ticket information in a view
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable listAllTicketInfo(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT * FROM TicketInfo");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * 4) Convert the name of FlightNumber to "Spanish" using aliasing
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable convertToEspanol(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT FlightNumber AS FlightNumero " +
											"FROM FlightInfo");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * 5) Find the amount of unique flights in the database using the count command
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable listUniqueFlightCount(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT COUNT(FlightID) AS TotalFlights FROM FlightInfo");
			
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * 6) Find the ticket with the lowest cost
	 *@param Date specificDate
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable listLowestCostFlight(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT MIN(Cost) AS LowestCost FROM "
															+ " TicketInfo");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * ?) Find information about a passenger by their last name
	 *@param String lastName
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable findInfoByLastName(String lastName){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT * FROM PassengerInfo WHERE" +
					" LastName IN(" + lastName +")");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}
	
	/**
	 * 7) Finds the list of planes due to fly out that are old planes
	 * 
	 * @return List of planes that are old
	 */
	static IDatabaseTable findOldPlanesDueToFlyOut() {
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT * FROM FlightInfo WHERE " +
					"PlaneID IN(OldPlanes)");
			return tableView;
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * 8) Find all tickets that cost more than $100
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable findTicketsGTHundred(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT * FROM TicketInfo WHERE" +
					" Cost > 100.00");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * 9) Get the list of passengers on high risk planes
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable findHighRiskPlanes(){
		IDatabaseTable tableView = null;
		try {
			String query = "SELECT * " + 
						   "FROM FlightInfo " + 
						   "WHERE ((" +
						   "(DestinationTimeInMin>100) AND " +
						   "(Destination!=LAX)) AND " +
						   "(NumberOfPassengers > 75))";
			tableView = airplaneApplication.select(query);
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 * 10) Return Last Person in the List
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable findLastPersonInList(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT LastName, FirstName " +
					" FROM PassengerInfo " +
					" WHERE PassengerID >= ALL(PassengerInfo)");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 *
	 *11) Return Passengers from Ticket Info using the ANY command
	 *@return IDataBaseTable tableView
	 *
	 */
	static IDatabaseTable getPassengersFromTicketInfo(){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT PassengerID FROM PassengerInfo"
					+ " WHERE PassengerID = ANY(TicketInfo)");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}

	/**
	 * ?) This opens a view in the GUI to display the table
	 */
	static void viewable(){
		try {
			IDatabaseTable able = createable();
			String[] olumnNames = able.getTableColumnNames();
			Object[][] DArray = able.getTableTuples();
			tableView viewTable = new tableView(olumnNames, DArray);
			viewTable.createAndShowGUI();
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
	}
	
	/**
	 * 12) Get all tickets for PassengerID 
	 * 
	 * @param passengerID
	 */
	static IDatabaseTable getTicketsByPassengerID(String passengerID){
		IDatabaseTable tableView = null;
		try {
			tableView = airplaneApplication.select("SELECT TicketInfo.TicketID, " +
					"TicketInfo.PassengerID, " +
					"PassengerInfo.LastName, " +
					"PassengerInfo.FirstName FROM " +
					"TicketInfo, PassengerInfo WHERE " +
					"((TicketInfo.PassengerID = PassengerInfo.PassengerID) AND " +
					"(TicketInfo.PassengerID = " + passengerID + "))");
		} catch (SqlException e) {
			System.out.println("There was a problem with your request : " + e.getMessage());
		}
		return tableView;
	}
	
	/**
	 * this opens a table view in the GUI and displays its members
	 * @param tableView
	 */
	static void viewTable(IDatabaseTable tableView){
//		System.out.println(tableView.getName());
//		System.out.println(tableView.getColumnLists().size());
//		System.out.println(tableView.getAllTuples().size());

		String[] columnNames = tableView.getTableColumnNames();
		Object[][] tuple2DArray = tableView.getTableTuples();
		tableView viewTable = new tableView(columnNames, tuple2DArray);
		viewTable.createAndShowGUI();
	}
	
	/**
	 * this creates a table to be printed to screen
	 * @return the database as a DatabaseAPI
	 * @throws SqlException 
	 */
	static IDatabaseTable createable() throws SqlException{
		/*create a list of IColumnDefitions for the table*/
		ArrayList<IColumnDefinition> olumns = new ArrayList<IColumnDefinition>();
		
		/*first column*/
		IColumnDefinition olumn1 = new ColumnDefinition(EnumColumnType.integer, "ColumnInt");
		/*second column*/
		IColumnDefinition olumn2 = new ColumnDefinition(EnumColumnType.floating, "ColumnFloat");
		/*third column*/
		IColumnDefinition olumn3 = new ColumnDefinition(EnumColumnType.varchar, "ColumnVarChar");
		
		/*add columns to list*/
		olumns.add(olumn1);
		olumns.add(olumn2);
		olumns.add(olumn3);
		
		/*create table*/
		IDatabaseTable able = new Table("Flight_Info",olumns);
		
		/*create four tupleElements*/
		ITupleElement upleElement = new TupleElement();
		ITupleElement tupleElem1 = upleElement.createTupleElement(10, "ColumnInt");
		ITupleElement tupleElem2 = upleElement.createTupleElement(10.5F, "ColumnFloat");		
		ITupleElement tupleElem3 = upleElement.createTupleElement("Howdy", "ColumnVarChar");
		ITupleElement tupleElem4 = upleElement.createTupleElement(15, "ColumnInt");
		ITupleElement tupleElem5 = upleElement.createTupleElement(9.5F, "ColumnFloat");
		ITupleElement tupleElem6 = upleElement.createTupleElement("Gig 'Em", "ColumnVarChar");
		
		
		/*create a two ITuples for the table*/
		ITuple uple1 = new Tuple();
		uple1.addTupleElement(tupleElem1);
		uple1.addTupleElement(tupleElem2);
		uple1.addTupleElement(tupleElem3);
		
		ITuple uple2 = new Tuple();
		uple2.addTupleElement(tupleElem4);
		uple2.addTupleElement(tupleElem5);
		uple2.addTupleElement(tupleElem6);

		
		able.addTuple(uple1);
		able.addTuple(uple2);

		
		return able;
	}
	
	/**
	 * Prints the welcome airplane
	 */
	static void welcomeGraphic(){
		System.out.println("**************************");
		System.out.println("       *           $$$$$$");
		System.out.println(" *    @**         $$$$$$$$ ");
		System.out.println(" **   @***         $$$$$$ ");
		System.out.println(" ***   ****");
		System.out.println("*****************");
		System.out.println(" *******************");
		System.out.println(" *********************");
		System.out.println(" ***   ****");
		System.out.println(" **   @*** #################");
		System.out.println(" *    @**  !     HOWDY!    !");
		System.out.println("       *   #################");
		System.out.println("\nThe \"Plane Jane\" Database System");
		System.out.println("Creators: Ben Unsworth, Christopher Weldon, Ryan McAuley");
		System.out.println("Version 1.0");
		System.out.println("********************************************************\n");
	}
}
